﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_DAO.db;
using Cashier_Model;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace Cashier_DAO
{
    public class GoodsDAO: DAOFactory
    {
        /// <summary>
        /// 查询商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public Goods GetGoods(DbConnection conn, Goods goods)
        {
            Goods result = null;
            try
            {
                StringBuilder sb = new StringBuilder("select g.*,c.name as categoryname from goods g,category c where g.category_id=c.id");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, goods);

                if (paramtable.Keys.Count > 0)
                {
                    DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                    this.AddCommandParamters(cmd, paramtable);
                    DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                    DataTable dt = new DataTable("goods");
                    da.Fill(dt);

                    if (dt != null && dt.Rows.Count == 1)
                    {
                        DataRow row = dt.Rows[0];
                        result = new Goods();
                        this.DataRowToModel(row, result);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 获取商品信息列表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public DataTable GetGoodsTable(DbConnection conn, Goods goods)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder("select g.*,c.name as categoryname from goods g,category c where g.category_id=c.id");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, goods);

                this.GenerateOrderAndPageSQL(sb, paramtable, goods);

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("goods");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 查询商品记录数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public long GetGoodsRecord(DbConnection conn, Goods goods)
        {
            long record = 0;
            try
            {
                StringBuilder sb = new StringBuilder("select count(g.id) as c from goods g where g.id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, goods);

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                Object obj = cmd.ExecuteScalar();
                if (obj != null)
                    record = (long)obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return record;
        }

        /// <summary>
        /// 插入商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public long InsertGoods(DbConnection conn, Goods goods)
        {
            long id = 0;
            try
            {
                id = (long)this.Insert(conn, goods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// 更新商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public int UpdateGoods(DbConnection conn, Goods goods)
        {
            int flag = 0;
            try
            {
                flag = this.Update(conn, goods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 更新商品单个字段信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public int UpdateGoodsSingleField(DbConnection conn, Goods goods)
        {
            int flag = 0;
            try
            {
                StringBuilder sql = new StringBuilder("update goods set");
                Hashtable paramtable = new Hashtable();
                if (goods.Pricecost > 0)
                {
                    sql.Append(" price_cost=@pricecost,");
                    paramtable.Add("pricecost", goods.Pricecost);
                }
                if (goods.Pricesale > 0)
                {
                    sql.Append(" price_sale=@pricesale,");
                    paramtable.Add("pricesale", goods.Pricesale);
                }
                if (goods.Discount > 0)
                {
                    sql.Append(" discount=@discount,");
                    paramtable.Add("discount", goods.Discount);
                }
                if (goods.Isvipprice != null)
                {
                    sql.Append(" is_vipprice=@vipprice,");
                    paramtable.Add("vipprice", goods.Isvipprice);
                }
                if (paramtable.Keys.Count > 0)
                {
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(" where id=@id");
                    paramtable.Add("id", goods.Id);

                    DbCommand cmd = CommandFactory.CreateCommand(sql.ToString(), conn);
                    this.AddCommandParamters(cmd, paramtable);
                    flag = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 更新商品库存
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public int UpdateGoodsStock(DbConnection conn, Goods goods)
        {
            int flag = 0;
            try
            {
                String sql = "update goods set stock=@stock where id=@id";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                Hashtable paramtable = new Hashtable();
                paramtable.Add("stock", goods.Stock);
                paramtable.Add("id", goods.Id);
                this.AddCommandParamters(cmd, paramtable);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 删除商品信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public int DeleteGoods(DbConnection conn, Goods goods)
        {
            int count = 0;
            try
            {
                count = this.Delete(conn, goods);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }

        /// <summary>
        /// 查询商品各分类数量
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="goods"></param>
        /// <returns></returns>
        public DataTable GetCategoryGroupTable(DbConnection conn, Goods goods)
        {
            DataTable dt = null;
            try
            {
                String sql = "select category_id,count(id) as group_count,(select name from category c where c.id=g.category_id) as category_name" +
                    " from goods g group by category_id order by group_count desc";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("category_group");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
